import datetime
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill


def get_same_sheet_name(Raw_daily, Raw_list):
    date = datetime.datetime.now().strftime('%m%d%Y')
    date_str = str(date[-4:]) + '-' + str(date[:-6]) + '-' + str(date[-6:-4])
    same_names_dic = {}
    daily_sheet_name = Raw_daily.sheetnames
    for file in Raw_list:
        same_sheet_names = []
        file_sheet_names = file.sheetnames
        for file_sheet_name in file_sheet_names:
            if file_sheet_name in daily_sheet_name:
                max_row = file[file_sheet_name].max_row
                for i in range(max_row):
                    i += 1
                    # 如果有更新當天的日期，就append這個sheet名到列表中，然後執行後續操作
                    if date_str in str(file[file_sheet_name]['D' + str(i)].value):
                        same_sheet_names.append(file_sheet_name)
                        same_names_dic[file] = same_sheet_names
                        break

    return same_names_dic


# 删除daily_report中某个sheet的内容
def del_daily_report_same_sheet(sheet_name, Raw_daily):
    daily_sheet = Raw_daily[sheet_name]
    daily_max_row = daily_sheet.max_row
    daily_max_col = daily_sheet.max_column
    for row in range(1, daily_max_row + 1):
        for col in range(1, daily_max_col + 1):
            daily_sheet.cell(row, col).value = None


def get_fail_row(table_tem):
    date_str = datetime.datetime.now().strftime("%Y/%m/%d")
    datetime_obj = datetime.datetime.strptime(date_str, '%Y/%m/%d')
    fail_row_list = []
    k = 2
    while True:
        cell_d_now = table_tem.cell(k, 4).value
        cell_m_now = table_tem.cell(k, 13).value
        if cell_d_now is None:
            return fail_row_list
        if cell_d_now == datetime_obj and (cell_m_now == "Fail" or cell_m_now == "COF"):
            fail_row_list.append(k)
        k += 1


# 传入sheet、表中所有fail的行数，写入数据
def handle_sheet_from_fail_row(table_tem, fail_row_list, test_log_sheet):
    date_str = datetime.datetime.now().strftime("%Y/%m/%d")
    datetime_obj = datetime.datetime.strptime(date_str, '%Y/%m/%d')
    cell_blue = Font(color="FF0000FF", sz=12.0, name="Times New Roman")
    # 对齐方式：居中
    cell_alignment_center = Alignment(horizontal='center', vertical='center', textRotation=0,
                                      wrapText=None, shrinkToFit=None, indent=0.0, relativeIndent=0.0,
                                      justifyLastLine=None, readingOrder=0.0)
    # 对齐方式：左对齐
    cell_alignment_left = Alignment(horizontal='left', vertical='center', textRotation=0, wrapText=None,
                                    shrinkToFit=None, indent=0.0, relativeIndent=0.0,
                                    justifyLastLine=None, readingOrder=0.0)

    # 边框类
    cell_border = Border(left=Side(border_style='thin', color='FF000000'),
                         right=Side(border_style='thin', color='FF000000'),
                         top=Side(border_style='thin', color='FF000000'),
                         bottom=Side(border_style='thin', color='FF000000')
                         )

    # 单元格填充颜色
    red_fill = PatternFill("solid", fgColor="FF0000")
    # green_fill = PatternFill("solid", fgColor="FF00B050")

    table_row = table_tem.max_row
    table_col = table_tem.max_column

    table_type_list = []
    for i in fail_row_list:
        p_value = table_tem.cell(i, 16).value
        cyc_type = [1]
        if "]:1[" in p_value:
            cyc_row_list = p_value[:-2].split(":1")
            len_cyc_list = len(cyc_row_list)
            for ki in range(len_cyc_list):
                if not ki:
                    continue
                if cyc_row_list[ki] == cyc_row_list[ki - 1]:
                    cyc_type[-1] += 1
                else:
                    cyc_type.append(1)
        table_type_list.append(cyc_type)

    # print("table_type_list:", table_type_list)
    log_col = test_log_sheet.max_row + 1
    while True:
        if test_log_sheet.cell(log_col - 1, 7).value or test_log_sheet.cell(log_col - 1, 3).value:
            break
        else:
            log_col -= 1

    # 合并C列单元格
    tem_num = log_col
    to_log_row = 0
    # table_type_list: [[2, 2, 1], [1], [1, 1]]
    # for 2 in [2, 2, 1]
    for i in table_type_list:
        len_tem = len(i)
        to_log_row += len_tem
    log_col_end = log_col + to_log_row - 1

    # 合并A、B列单元格
    test_log_sheet.merge_cells("A" + str(log_col) + ":" + "A" + str(log_col_end))
    test_log_sheet.merge_cells("B" + str(log_col) + ":" + "B" + str(log_col_end))

    fail_table_len = len(fail_row_list)
    for i in range(fail_table_len):
        if not i:
            # A列
            test_log_sheet.cell(log_col, 1).value = datetime_obj
            test_log_sheet.cell(log_col, 1).font = cell_blue
            test_log_sheet.cell(log_col, 1).number_format = 'm/d;@'
            test_log_sheet.cell(log_col, 1).alignment = cell_alignment_center
            test_log_sheet.cell(log_col, 1).border = cell_border

            # B列
            test_log_sheet.cell(log_col, 2).value = table_tem.title
            test_log_sheet.cell(log_col, 2).font = cell_blue
            test_log_sheet.cell(log_col, 2).alignment = cell_alignment_center
            test_log_sheet.cell(log_col, 2).border = cell_border

        O_split_list = []
        P_split_list = []
        N_split_list = []
        Q_split_list = []
        if len(table_type_list[i]) > 1 or (len(table_type_list[i]) == 1 and table_type_list[i][0] > 1):
            O_value = table_tem.cell(fail_row_list[i], 15).value[:-2]
            O_split_list = O_value.split(":1")
            P_value = table_tem.cell(fail_row_list[i], 16).value[:-2]
            P_split_list = P_value.split(":1")
            N_value = table_tem.cell(fail_row_list[i], 14).value[:-2]
            N_split_list = N_value.split(":1")
            Q_value = table_tem.cell(fail_row_list[i], 17).value[:-2]
            Q_split_list = Q_value.split(":1")

        # table_type_list: [[2, 2, 1], [1], [1, 1]]
        # for 2 in [2, 2, 1]
        index_begin = 0
        for fail_cyc_row in table_type_list[i]:

            # ABCDEFGHI列边框、字体颜色设置
            for j in range(1, 10):
                test_log_sheet.cell(log_col, j).border = cell_border
                test_log_sheet.cell(log_col, j).font = cell_blue

            # C列
            if len(table_type_list[i]) > 1:
                P_to_log = P_split_list[fail_cyc_row + index_begin - 1]
            else:
                # 同个CYC多个Fail
                if fail_cyc_row > 1:
                    P_to_log = P_split_list[0]
                else:
                    P_to_log = table_tem.cell(fail_row_list[i], 16).value
            P_to_log = P_to_log[1:-1]
            # print("P_to_log:", P_to_log)
            test_log_sheet.cell(log_col, 3).value = P_to_log
            test_log_sheet.cell(log_col, 3).alignment = cell_alignment_left

            # D列 + F列
            F_with_1 = "1F/"
            for j in range(table_col, 18, -1):
                if table_tem.cell(1, j).value == P_to_log:
                    D_row = 0
                    for k in range(2, table_row + 1):
                        D_tem_value = table_tem.cell(k, j).value
                        if D_tem_value == "COF" or D_tem_value == "Pass" or D_tem_value == "Fail":
                            D_row += 1
                    test_log_sheet.cell(log_col, 4).value = D_row
                    F_value = F_with_1 + str(D_row)
                    test_log_sheet.cell(log_col, 6).value = F_value
                    test_log_sheet.cell(log_col, 6).fill = red_fill
                    break
            test_log_sheet.cell(log_col, 4).alignment = cell_alignment_center
            test_log_sheet.cell(log_col, 6).alignment = cell_alignment_center

            # G列
            test_log_sheet.cell(log_col, 7).value = table_tem.cell(fail_row_list[i], 2).value
            test_log_sheet.cell(log_col, 7).alignment = cell_alignment_left

            # H列
            test_log_sheet.cell(log_col, 8).value = table_tem.cell(fail_row_list[i], 1).value
            test_log_sheet.cell(log_col, 8).alignment = cell_alignment_left

            # I列
            if len(table_type_list[i]) > 1:
                N_to_log = ((N_split_list[fail_cyc_row + index_begin - 1] + ":1") * fail_cyc_row)[:-2]
                Q_to_log = ((Q_split_list[fail_cyc_row + index_begin - 1] + ":1") * fail_cyc_row)[:-2]
                O_to_log = O_split_list[fail_cyc_row + index_begin - 1]
            else:
                N_to_log = table_tem.cell(fail_row_list[i], 14).value
                Q_to_log = table_tem.cell(fail_row_list[i], 17).value
                O_to_log = table_tem.cell(fail_row_list[i], 15).value
            # print("O_to_log:", O_to_log, "N_to_log:", N_to_log, "Q_to_log:", Q_to_log)

            # 同个cyc中多个Fail的情况
            if fail_cyc_row > 1:
                # I_value = ""
                O_split_I = O_to_log[:-2].split(":1")
                N_split_I = N_to_log[:-2].split(":1")
                Q_split_I = Q_to_log[:-2].split(":1")
                I_value = O_split_I[0]
                for split_num in range(len(O_split_I)):
                    I_split = N_split_I[split_num] + Q_split_I[split_num] + ":1"
                    I_value += I_split
                test_log_sheet.cell(log_col, 9).value = "@" + I_value[:-2]
            else:
                test_log_sheet.cell(log_col, 9).value = "@" + O_to_log + N_to_log + Q_to_log
            test_log_sheet.cell(log_col, 9).alignment = cell_alignment_left

            index_begin += fail_cyc_row
            log_col += 1

    # C、D、F列合并相同项
    # [[1, 2], [5, 8], [11, 14]]
    C_merge_list = []
    for i in range(tem_num, log_col_end + 1):

        if len(C_merge_list) > 0 and C_merge_list[-1][0] <= i <= C_merge_list[-1][1]:
            continue

        k = 1
        while True:
            if test_log_sheet.cell(i, 3).value == test_log_sheet.cell(i + k, 3).value:
                C_merge_list.append([i, i + k])
                k += 1
            else:
                if k > 1:
                    for j in range(k - 2):
                        del C_merge_list[-2]
                break

    # print("C_merge_list:", C_merge_list)
    # print("tem_num:", tem_num, "log_col_end:", log_col_end)
    for i in C_merge_list:
        F_str_to_list = list(test_log_sheet.cell(i[0], 6).value)
        F_str_to_list[0] = str(i[1] - i[0] + 1)
        test_log_sheet.cell(i[0], 6).value = ''.join(F_str_to_list)
        test_log_sheet.merge_cells(start_row=i[0], start_column=3, end_row=i[1], end_column=3)
        test_log_sheet.merge_cells(start_row=i[0], start_column=4, end_row=i[1], end_column=4)
        test_log_sheet.merge_cells(start_row=i[0], start_column=6, end_row=i[1], end_column=6)


def Data_Generate(Raw_daily, Raw_list):
    same_names_dic = get_same_sheet_name(Raw_daily, Raw_list)

    blue_sheet_list = []
    for key, value in same_names_dic.items():
        for same_sheet_name in value:
            # 删除daily_report中与细项表中同名sheet的内容
            del_daily_report_same_sheet(same_sheet_name, Raw_daily)
            # 读取file和daily_report中同名的sheet
            file_sheet = key[same_sheet_name]
            daily_sheet = Raw_daily[same_sheet_name]
            # 获取file_sheet最大行、列数
            file_sheet_max_row = file_sheet.max_row
            file_sheet_max_col = file_sheet.max_column
            # 将fail中sheet的值赋给daily_report与他同名sheet
            for row in range(1, file_sheet_max_row + 1):
                for col in range(1, file_sheet_max_col + 1):
                    daily_sheet.cell(row, col).value = file_sheet.cell(row, col).value
            # 更新sheet标签颜色
            Raw_daily[same_sheet_name].sheet_properties.tabColor = "FF0000FF"

            blue_sheet_list.append(same_sheet_name)

    for i in blue_sheet_list:
        table_tem = Raw_daily[i]
        try:
            # 传入sheet，查找D列是否为当天日期，M列是否为Fail与COF，返回符合的row列表
            fail_row_list = get_fail_row(table_tem)
            # print("table_tem:", table_tem, "fail_row_list:", fail_row_list)
            # 传入sheet、fail行数 写入数据
            handle_sheet_from_fail_row(table_tem, fail_row_list, Raw_daily["TestLog"])
        except:
            pass

    return Raw_daily
